package com.acxy.ants.core.plugin.db;

import com.acxy.ants.common.enums.TxLevel;
import com.acxy.ants.common.kit.JsonMap;
import com.acxy.ants.common.kit.PropKit;
import com.acxy.ants.common.kit.StrCaseKit;
import com.acxy.ants.core.exception.SQLParamsException;
import com.acxy.ants.core.IBean;
import com.acxy.ants.resful.bean.Page;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * @author MrShun
 * @version 1.0
 * @Date 2017-09-06
 */
public class Db {

    private static final Logger logger = LoggerFactory.getLogger(Db.class);

    protected ThreadLocal<Connection> connections = new ThreadLocal<>();

    protected DataSource dataSource;

    private String url, className, username, password;

    //数据源连接带连接池
    public Db(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Connection getConnection() {
        Connection conn = null;
        if (dataSource == null) {
            try {
                Class.forName(className);
                conn = DriverManager.getConnection(url, username, password);
                connections.set(conn);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                logger.debug("原生加载 com.mysql.jdbc.Driver 失败!");
            } catch (SQLException e) {
                e.printStackTrace();
                logger.debug("原生连接数据库失败, 请认真检查配置!");
            }

        } else {
            try {
                conn = dataSource.getConnection();
                connections.set(conn);
            } catch (SQLException e) {
                e.printStackTrace();
                logger.debug("获取数据源失败, 请认真检查配置!");
            }
        }
        return conn;
    }

    //原生连接不带连接池
    public Db(String url, String className, String username, String password) {
        this.url = url;
        this.className = className;
        this.username = username;
        this.password = password;
    }

    //开启事物
    public void startTx(DataSource dataSource, String currentSource, TxLevel level) {
        if (dataSource == null)
            throw new RuntimeException(currentSource + " 没有配置数据源, 错误!");
        Connection conn = connections.get();
        //如果在当前线程中没有绑定相应的connection
        if (conn == null) {
            try {
                conn = dataSource.getConnection();
                connections.set(conn);
                conn.setAutoCommit(false);
                conn.setTransactionIsolation(level.level());
                logger.debug(currentSource + "start transactional level {} [{}] !", level, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public void commit() throws SQLException {
        Connection conn = connections.get();
        if (conn != null) {
            conn.commit();
            close(conn);
            logger.debug("Commit transactional {} !", conn);
        }
    }

    public void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
                //从ThreadLocal中清除Connection
                connections.remove();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void close(PreparedStatement ps) {
        close(null, ps);
    }

    public void close(ResultSet rs, PreparedStatement ps) {
        try {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void rollback() {
        Connection conn = connections.get();
        try {
            if (conn != null) conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        logger.debug("RollBack transactional {} !", conn);
    }


    /**
     * 批处理
     *
     * @param sql    预处理语句
     * @param params 参数
     * @return
     */
    public int[] batch(String sql, Object[]... params) {
        int[] rows = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql);
            if (params != null && params.length != 0) {
                for (int i = 0; i < params.length; i++) {
                    fillStatement(sql, ps, params[i]);
                    ps.addBatch();
                }
            }
            int[] res = ps.executeBatch();
            if(check) close(conn);
            return res;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLParamsException(e);
        }finally {
            close(rs, ps);
        }
    }

    /**
     * 批处理返回主键
     *
     * @param sql    预处理语句
     * @param params 参数
     * @return
     */
    public Long[] batchReturnKey(String sql, Object[]... params) {
        Long[] keys = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (params != null && params.length != 0) {
                for (int i = 0; i < params.length; i++) {
                    fillStatement(sql, ps, params[i]);
                    ps.addBatch();
                }
            }
            ps.executeBatch();
            rs = ps.getGeneratedKeys();
            if (rs.first()) {
                keys = new Long[rs.getRow()];
                int i = 0;
                while (rs.next()) {
                    keys[i] = rs.getLong(1);
                    i++;
                }
            }
            if(check) close(conn);
            return keys;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLParamsException(e);
        }finally {
            close(rs, ps);
        }
    }

    /**
     * 保存数据带放回主键Key
     *
     * @param sql    预处理语句
     * @param params 参数
     * @return
     */
    public Long insertReturnKey(String sql, Object... params) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            fillStatement(sql, ps, params);
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.first()) {
                return rs.getLong(1);
            }
            if(check) close(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLParamsException(e);
        } finally {
            close(rs, ps);
        }
        return null;
    }

    /**
     * 保存数据不带放回主键Key
     *
     * @param sql    预处理语句
     * @param params 参数
     * @return
     */
    public void insert(String sql, Object... params) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            fillStatement(sql, ps, params);
            ps.execute();
            if(check) close(conn);
        } catch (SQLException e) {
            throw new SQLParamsException(e);
        }finally {
            close(ps);
        }
    }

    public int delete(String sql, Object... params) {
        return update(sql, params);
    }
    public int delete(String sql) {
        return delete(sql, null);
    }

    /**
     * 修改数据
     *
     * @param sql    预处理语句
     * @param params 参数
     * @return
     */
    public int update(String sql, Object... params) {
        PreparedStatement ps = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql);
            fillStatement(sql, ps, params);
            int res = ps.executeUpdate();
            if(check) close(conn);
            return res;
        } catch (SQLException e) {
            throw new SQLParamsException(e);
        } finally {
           close(ps);
        }
    }
    public int update(String sql){return update(sql, null);}

    /**
     * 查询单条数据放入到JsonMap中
     *
     * @param sql    预处理语句
     * @param params 对象参数数组
     * @return
     */
    public JsonMap query(String sql, Object... params) {
        JsonMap result = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql);
            fillStatement(sql, ps, params);
            rs = ps.executeQuery();
            if (rs.first()) {
                result = JsonMap.newJsonMap();
                ResultSetMetaData rsm = rs.getMetaData(); //获得列集
                for (int j = 1; j <= rsm.getColumnCount(); j++) {
                    String columnName = rsm.getColumnLabel(j);
                    Object val = rs.getObject(columnName);
                    result.set(columnName, val);
                }
            }
            if(check) close(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLParamsException(e);
        } finally {
            close(rs, ps);
        }
        return result;
    }

    public JsonMap query(String sql) {
        return query(sql, new Object[]{});
    }

    /**
     * 查询列表结果填充JsonMap
     *
     * @param sql    预处理语句
     * @param params
     * @return
     */
    public List<JsonMap> list(String sql, Object... params) {
        List<JsonMap> result = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql);
            fillStatement(sql, ps, params);
            rs = ps.executeQuery();
            ResultSetMetaData rsm = rs.getMetaData(); //获得列集
            while (rs.next()) {
                JsonMap jsonMap = JsonMap.newJsonMap();
                for (int j = 1; j <= rsm.getColumnCount(); j++) {
                    String columnName = rsm.getColumnLabel(j);
                    Object val = rs.getObject(columnName);
                    jsonMap.set(columnName, val);
                }
                result.add(jsonMap);
            }
            if(check) close(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLParamsException(e);
        } finally {
            close(rs, ps);
        }
        return result;
    }

    public List<JsonMap> list(String sql) {
        return list(sql, new Object[]{});
    }

    /**
     * 查询单条对象将结果反射到对象里面
     *
     * @param sql    预处理语句
     * @param obj    对象
     * @param params 对象参数数组
     * @param <T>    对象必须继承IBean
     * @return
     */
    public <T extends IBean> T query(String sql, T obj, Object... params) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql);
            fillStatement(sql, ps, params);
            rs = ps.executeQuery();
            if (rs.first()) {
                ResultSetMetaData rsm = rs.getMetaData(); //获得列集
                Field[] fields = obj.getClass().getDeclaredFields();
                setColumns(rs, rsm, fields, obj);
            } else
                obj = null;
            if(check) close(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLParamsException(e);
        } finally {
            close(rs, ps);
        }
        return obj;
    }

    public <T extends IBean> T query(String sql, T obj) {
        return (T) query(sql, obj, null);
    }

    /**
     * 查询列表反射填充对象
     *
     * @param sql    预处理语句
     * @param cls    对象Class
     * @param params 对象参数数组
     * @param <T>    对象必须继承IBean
     * @return
     */
    public <T extends IBean> List<T> list(String sql, Class<T> cls, Object... params) {
        List<T> result = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            boolean check = false;
            Connection conn = connections.get();
            if (conn == null) {
                conn = getConnection();
                check = true;
            }
            if (sql == null) {
                if (conn != null) {
                    conn.close();
                }
                throw new SQLException("Null SQL statement");
            }
            ps = conn.prepareStatement(sql);
            fillStatement(sql, ps, params);
            rs = ps.executeQuery();
            ResultSetMetaData rsm = rs.getMetaData(); //获得列集
            Field[] fields = cls.getDeclaredFields();
            while (rs.next()) {
                T obj = cls.newInstance();
                setColumns(rs, rsm, fields, obj);
                result.add(obj);
            }
            if(check) close(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLParamsException(e);
        } catch (InstantiationException e) {
            e.printStackTrace();
            throw new IllegalArgumentException(e);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new IllegalArgumentException(e);
        } finally {
            close(rs, ps);
        }
        return result;
    }

    public <T extends IBean> List<T> list(String sql, Class<T> cls) {
        return list(sql, cls, null);
    }


    public <T> Page page(String sql, Integer pageIndex, Integer pageSize, Object... params) {
        return page(sql, null, pageIndex, pageSize, params);
    }

    public <T extends IBean> Page page(String sql, Class<T> cls, Integer pageIndex, Integer pageSize, Object... params) {
        int page = (pageIndex == null || pageIndex <= 0) ? 0 : pageIndex;
        int size = (pageSize == null || pageSize < 0) ? 10 : pageSize;
        StringBuffer sb = new StringBuffer(sql);
        sb.append(" limit " + (page * size) + "," + size);
        List data = (cls == null) ? list(sb.toString(), params) : list(sb.toString(), cls, params);
        String count_sql = "select count(1) as count " + sql.substring(sql.toUpperCase().indexOf("FROM"), sql.length());
        long rows = query(count_sql, params).getLong("count");
        int total = (int) (rows / size + (rows % size == 0 ? 0 : 1));
        return new Page(page, size, data, rows, total);
    }

    //私有方法填充参数
    private void fillStatement(String sql, PreparedStatement ps, Object... params) throws SQLException {
        if (params == null || params.length == 0) return;
        for (int i = 1; i <= params.length; i++) {
            ps.setObject(i, params[i - 1]);
        }
        if(PropKit.getBoolean("debug")) logger.debug("\nSQL    : {}\nParams : {}\n", sql, JSON.toJSON(params));
    }

    //私有方法填充列
    private void setColumns(ResultSet rs, ResultSetMetaData rsm, Field[] fields, Object obj) throws SQLException {
        for (int j = 1; j <= rsm.getColumnCount(); j++) {
            String columnName = rsm.getColumnLabel(j);
            Object val = rs.getObject(columnName);
            for (Field field : fields) {
                if (StrCaseKit.toUnderlineName(columnName).equals(StrCaseKit.toUnderlineName(field.getName()))) {
                    field.setAccessible(true);
                    try {
                        field.set(obj, val);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    break;
                }
            }
        }
    }
}
